using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using CavemanTools.Extensions;

namespace CavemanTools.Sql
{
	/// <summary>
	/// Extension methods for sql queries
	/// </summary>
	public static class NeutralSqlExtensions
	{
		#region Insert
		/// <summary>
		/// Create sql insert statement string from object
		/// </summary>
		/// <param name="values">Anonymous object, properties are columns names</param>
		/// <param name="table">Table name</param>
		/// <exception cref="ArgumentNullException"></exception>
		/// <returns></returns>
		public static string GenerateInsertSql(this object values, string table)
		{
			return GenerateInsertSql(values, table, new SqlParameterFormatter().Format);
		}


		/// <summary>
		/// Create sql insert statement string from object
		/// </summary>
		/// <param name="values">Anonymous object, properties are columns names</param>
		/// <param name="table">Table name</param>
		/// <param name="paramFormatter">Sql Parameter name formatter</param>
		/// <exception cref="ArgumentNullException"></exception>
		/// <returns></returns>
		public static string GenerateInsertSql(this object values, string table, Func<string, string> paramFormatter)
		{
			if (values == null) throw new ArgumentNullException("values");
			if (paramFormatter == null) throw new ArgumentNullException("paramFormatter");
			if (string.IsNullOrEmpty(table)) throw new ArgumentNullException("table");
			var sb = new StringBuilder("insert into ");
			sb.AppendFormat("{0}(", table);
			var data = values.ToDictionary();
			byte i = 1;
			foreach (var name in data.Keys)
			{
				sb.Append(name);
				if (i < data.Count) sb.Append(",");
				i++;
			}
			sb.Append(") values(");
			i = 1;
			foreach (var name in data.Keys)
			{
				sb.Append(paramFormatter(name));
				if (i < data.Count) sb.Append(",");
				i++;
			}
			sb.Append(")");
			return sb.ToString();
		}
		#endregion

		//#region UpdateSql
		///// <summary>
		///// Creates update sql statement string from object
		///// </summary>
		///// <param name="values">Anonymous object, properties are columns names</param>
		///// <param name="table">Table name</param>
		///// <exception cref="ArgumentNullException"></exception>
		///// <returns></returns>
		//public static string GenerateUpdateSql(this object values, string table,string where)
		//{
		//    return GenerateUpdateSql(values, table,where, new SqlParameterFormatter().Format);
		//}



		///// <summary>
		///// Create update sql statement string from object
		///// </summary>
		///// <param name="values">Anonymous object, properties are columns names</param>
		///// <param name="table">Table name</param>
		///// <param name="paramFormatter">Sql Parameter name formatter</param>
		///// <exception cref="ArgumentNullException"></exception>
		///// <returns></returns>
		//public static string GenerateUpdateSql(this object values, string table, string where, Func<string, string> paramFormatter)
		//{
		//    if (values == null) throw new ArgumentNullException("values");
		//    if (paramFormatter == null) throw new ArgumentNullException("paramFormatter");
		//    if (string.IsNullOrEmpty(table)) throw new ArgumentNullException("table");
		//    var sb = new StringBuilder("update ");
		//    sb.AppendFormat("{0} set ", table);
		//    var data = values.ToDictionary();
		//    byte i = 1;
		//    foreach (var name in data.Keys)
		//    {
		//        sb.AppendFormat("{0}={1}", name, paramFormatter(name));
		//        if (i < data.Count) sb.Append(",");
		//        i++;
		//    }
		//    if (!string.IsNullOrEmpty(where))
		//    {
		//        sb.AppendFormat(" where {0}", where);
		//    }
		//    return sb.ToString();
		//} 
		//#endregion

		/// <summary>
		/// Returns a list of T from query or an empty list if query has no results
		/// </summary>
		/// <typeparam name="T">Type to convert data row</typeparam>
		/// <param name="cmd">Command Object</param>
		/// <returns>List of T</returns>
		public static IList<T> QueryList<T>(this IDbCommand cmd) where T : new()
		{
			if (cmd == null) throw new ArgumentNullException("cmd");
			var l = new List<T>();
			var props = (typeof(T)).GetProperties(BindingFlags.Public | BindingFlags.Instance);
			using (var rd = cmd.ExecuteReader())
			{
				while (rd.Read())
				{
					var t = new T();
					FillData(rd, t, props);
					l.Add(t);
				}
			}
			return l;
		}

		/// <summary>
		/// Executes query an returns the first value of the first row in the resultset converted to type T
		/// </summary>
		/// <typeparam name="T">Type to convert value</typeparam>
		/// <param name="cmd"></param>
		/// <returns></returns>
		public static ScalarResult<T> QuerySingle<T>(this IDbCommand cmd)
		{
			if (cmd == null) throw new ArgumentNullException("cmd");
				var val = cmd.ExecuteScalar();
				if (val == null) return new ScalarResult<T>();
			return new ScalarResult<T>(val.ConvertTo<T>());
		}

		/// <summary>
		/// Creates object of type T from data reader
		/// </summary>
		/// <typeparam name="T">Object Type</typeparam>
		/// <param name="rd">Data reader</param>
		/// <returns>Object</returns>
		public static T DataTo<T>(this IDataReader rd) where T : new()
		{
			var t = new T();
			FillData(rd, t, (typeof(T)).GetProperties(BindingFlags.Public | BindingFlags.Instance));
			return t;
		}

		private static void FillData<T>(IDataReader rd, T t, PropertyInfo[] properties)
		{
			for (int i = 0; i < rd.FieldCount; i++)
			{
				var column = rd.GetName(i);
				var prop = properties.FirstOrDefault(d => d.Name.Equals(column));
				if (prop == null)
				{
					//property does not exist
					continue;
				}
				prop.SetValue(t, rd.GetValue(i), null);
			}

		}
		

		
	}
}